using Maticsoft.DBUtility;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace uTrade.Data
{
    public class StockMinInfoManager
    {
        public StockMinInfoManager()
        { }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public static int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("Symbol", "tbl_Stock_MinInfo");
        }

        /// <summary>
        /// 清空
        /// </summary>
        public static int Clear()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Stock_MinInfo ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbl_Stock_MinInfo");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(StockMinInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbl_Stock_MinInfo(");
            strSql.Append("Symbol, Name, Type, Open, High, Low, Status, Price, Yestclose, Percent, Updown, Arrow, Volume, Turnover, Ask1, Ask2, Ask3, Ask4, Ask5, Askvol1, Askvol2, Askvol3, Askvol4, Askvol5, Bid1, Bid2, Bid3, Bid4, Bid5, Bidvol1, Bidvol2, Bidvol3, Bidvol4, Bidvol5, Update, Time)");
            strSql.Append(" values (");
            strSql.Append("@Symbol, @Name, @Type, @Open, @High, @Low, @Status, @Price, @Yestclose, @Percent, @Updown, @Arrow, @Volume, @Turnover, @Ask1, @Ask2, @Ask3, @Ask4, @Ask5, @Askvol1, @Askvol2, @Askvol3, @Askvol4, @Askvol5, @Bid1, @Bid2, @Bid3, @Bid4, @Bid5, @Bidvol1, @Bidvol2, @Bidvol3, @Bidvol4, @Bidvol5, @Update, @Time)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@Symbol", SqlDbType.VarChar ,10),
new SqlParameter("@Name", SqlDbType.NVarChar ,50),
new SqlParameter("@Type", SqlDbType.NChar ,10),
new SqlParameter("@Open", SqlDbType.Decimal ,9),
new SqlParameter("@High", SqlDbType.Decimal ,9),
new SqlParameter("@Low", SqlDbType.Decimal ,9),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@Price", SqlDbType.Decimal ,9),
new SqlParameter("@Yestclose", SqlDbType.Decimal ,9),
new SqlParameter("@Percent", SqlDbType.Float),
new SqlParameter("@Updown", SqlDbType.Float),
new SqlParameter("@Arrow", SqlDbType.NChar ,10),
new SqlParameter("@Volume", SqlDbType.NVarChar ,50),
new SqlParameter("@Turnover", SqlDbType.Decimal ,9),
new SqlParameter("@Ask1", SqlDbType.Decimal ,9),
new SqlParameter("@Ask2", SqlDbType.Decimal ,9),
new SqlParameter("@Ask3", SqlDbType.Decimal ,9),
new SqlParameter("@Ask4", SqlDbType.Decimal ,9),
new SqlParameter("@Ask5", SqlDbType.Decimal ,9),
new SqlParameter("@Askvol1", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol2", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol3", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol4", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol5", SqlDbType.NVarChar ,50),
new SqlParameter("@Bid1", SqlDbType.Decimal ,9),
new SqlParameter("@Bid2", SqlDbType.Decimal ,9),
new SqlParameter("@Bid3", SqlDbType.Decimal ,9),
new SqlParameter("@Bid4", SqlDbType.Decimal ,9),
new SqlParameter("@Bid5", SqlDbType.Decimal ,9),
new SqlParameter("@Bidvol1", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol2", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol3", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol4", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol5", SqlDbType.NVarChar ,50),
new SqlParameter("@Update", SqlDbType.NVarChar ,50),
new SqlParameter("@Time", SqlDbType.DateTime)};
            parameters[0].Value = model.Symbol;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Type;
            parameters[3].Value = model.Open;
            parameters[4].Value = model.High;
            parameters[5].Value = model.Low;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.Price;
            parameters[8].Value = model.Yestclose;
            parameters[9].Value = model.Percent;
            parameters[10].Value = model.Updown;
            parameters[11].Value = model.Arrow;
            parameters[12].Value = model.Volume;
            parameters[13].Value = model.Turnover;
            parameters[14].Value = model.Ask1;
            parameters[15].Value = model.Ask2;
            parameters[16].Value = model.Ask3;
            parameters[17].Value = model.Ask4;
            parameters[18].Value = model.Ask5;
            parameters[19].Value = model.Askvol1;
            parameters[20].Value = model.Askvol2;
            parameters[21].Value = model.Askvol3;
            parameters[22].Value = model.Askvol4;
            parameters[23].Value = model.Askvol5;
            parameters[24].Value = model.Bid1;
            parameters[25].Value = model.Bid2;
            parameters[26].Value = model.Bid3;
            parameters[27].Value = model.Bid4;
            parameters[28].Value = model.Bid5;
            parameters[29].Value = model.Bidvol1;
            parameters[30].Value = model.Bidvol2;
            parameters[31].Value = model.Bidvol3;
            parameters[32].Value = model.Bidvol4;
            parameters[33].Value = model.Bidvol5;
            parameters[34].Value = model.Update;
            parameters[35].Value = model.Time;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static int Update(StockMinInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbl_Stock_MinInfo set ");
            strSql.Append("Name = @Name,");
            strSql.Append("Type = @Type,");
            strSql.Append("Open = @Open,");
            strSql.Append("High = @High,");
            strSql.Append("Low = @Low,");
            strSql.Append("Status = @Status,");
            strSql.Append("Price = @Price,");
            strSql.Append("Yestclose = @Yestclose,");
            strSql.Append("Percent = @Percent,");
            strSql.Append("Updown = @Updown,");
            strSql.Append("Arrow = @Arrow,");
            strSql.Append("Volume = @Volume,");
            strSql.Append("Turnover = @Turnover,");
            strSql.Append("Ask1 = @Ask1,");
            strSql.Append("Ask2 = @Ask2,");
            strSql.Append("Ask3 = @Ask3,");
            strSql.Append("Ask4 = @Ask4,");
            strSql.Append("Ask5 = @Ask5,");
            strSql.Append("Askvol1 = @Askvol1,");
            strSql.Append("Askvol2 = @Askvol2,");
            strSql.Append("Askvol3 = @Askvol3,");
            strSql.Append("Askvol4 = @Askvol4,");
            strSql.Append("Askvol5 = @Askvol5,");
            strSql.Append("Bid1 = @Bid1,");
            strSql.Append("Bid2 = @Bid2,");
            strSql.Append("Bid3 = @Bid3,");
            strSql.Append("Bid4 = @Bid4,");
            strSql.Append("Bid5 = @Bid5,");
            strSql.Append("Bidvol1 = @Bidvol1,");
            strSql.Append("Bidvol2 = @Bidvol2,");
            strSql.Append("Bidvol3 = @Bidvol3,");
            strSql.Append("Bidvol4 = @Bidvol4,");
            strSql.Append("Bidvol5 = @Bidvol5,");
            strSql.Append("Update = @Update,");
            strSql.Append("Time = @Time,");

            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@Symbol", SqlDbType.VarChar ,10),
new SqlParameter("@Name", SqlDbType.NVarChar ,50),
new SqlParameter("@Type", SqlDbType.NChar ,10),
new SqlParameter("@Open", SqlDbType.Decimal ,9),
new SqlParameter("@High", SqlDbType.Decimal ,9),
new SqlParameter("@Low", SqlDbType.Decimal ,9),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@Price", SqlDbType.Decimal ,9),
new SqlParameter("@Yestclose", SqlDbType.Decimal ,9),
new SqlParameter("@Percent", SqlDbType.Float),
new SqlParameter("@Updown", SqlDbType.Float),
new SqlParameter("@Arrow", SqlDbType.NChar ,10),
new SqlParameter("@Volume", SqlDbType.NVarChar ,50),
new SqlParameter("@Turnover", SqlDbType.Decimal ,9),
new SqlParameter("@Ask1", SqlDbType.Decimal ,9),
new SqlParameter("@Ask2", SqlDbType.Decimal ,9),
new SqlParameter("@Ask3", SqlDbType.Decimal ,9),
new SqlParameter("@Ask4", SqlDbType.Decimal ,9),
new SqlParameter("@Ask5", SqlDbType.Decimal ,9),
new SqlParameter("@Askvol1", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol2", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol3", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol4", SqlDbType.NVarChar ,50),
new SqlParameter("@Askvol5", SqlDbType.NVarChar ,50),
new SqlParameter("@Bid1", SqlDbType.Decimal ,9),
new SqlParameter("@Bid2", SqlDbType.Decimal ,9),
new SqlParameter("@Bid3", SqlDbType.Decimal ,9),
new SqlParameter("@Bid4", SqlDbType.Decimal ,9),
new SqlParameter("@Bid5", SqlDbType.Decimal ,9),
new SqlParameter("@Bidvol1", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol2", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol3", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol4", SqlDbType.NVarChar ,50),
new SqlParameter("@Bidvol5", SqlDbType.NVarChar ,50),
new SqlParameter("@Update", SqlDbType.NVarChar ,50),
new SqlParameter("@Time", SqlDbType.DateTime)};
            parameters[0].Value = model.Symbol;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Type;
            parameters[3].Value = model.Open;
            parameters[4].Value = model.High;
            parameters[5].Value = model.Low;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.Price;
            parameters[8].Value = model.Yestclose;
            parameters[9].Value = model.Percent;
            parameters[10].Value = model.Updown;
            parameters[11].Value = model.Arrow;
            parameters[12].Value = model.Volume;
            parameters[13].Value = model.Turnover;
            parameters[14].Value = model.Ask1;
            parameters[15].Value = model.Ask2;
            parameters[16].Value = model.Ask3;
            parameters[17].Value = model.Ask4;
            parameters[18].Value = model.Ask5;
            parameters[19].Value = model.Askvol1;
            parameters[20].Value = model.Askvol2;
            parameters[21].Value = model.Askvol3;
            parameters[22].Value = model.Askvol4;
            parameters[23].Value = model.Askvol5;
            parameters[24].Value = model.Bid1;
            parameters[25].Value = model.Bid2;
            parameters[26].Value = model.Bid3;
            parameters[27].Value = model.Bid4;
            parameters[28].Value = model.Bid5;
            parameters[29].Value = model.Bidvol1;
            parameters[30].Value = model.Bidvol2;
            parameters[31].Value = model.Bidvol3;
            parameters[32].Value = model.Bidvol4;
            parameters[33].Value = model.Bidvol5;
            parameters[34].Value = model.Update;
            parameters[35].Value = model.Time;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static bool Delete(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Stock_MinInfo ");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public static int DeleteList(string symbollist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Stock_MinInfo ");
            strSql.Append(" where Symbol in (" + symbollist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static StockMinInfo GetModel(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Symbol, Name, Type, Open, High, Low, Status, Price, Yestclose, Percent, Updown, Arrow, Volume, Turnover, Ask1, Ask2, Ask3, Ask4, Ask5, Askvol1, Askvol2, Askvol3, Askvol4, Askvol5, Bid1, Bid2, Bid3, Bid4, Bid5, Bidvol1, Bidvol2, Bidvol3, Bidvol4, Bidvol5, Update, Time from tbl_Stock_MinInfo ");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            StockMinInfo model = new StockMinInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ModelConvertHelper<StockMinInfo>.ConvertToModel(ds.Tables[0])?[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Symbol, Name, Type, Open, High, Low, Status, Price, Yestclose, Percent, Updown, Arrow, Volume, Turnover, Ask1, Ask2, Ask3, Ask4, Ask5, Askvol1, Askvol2, Askvol3, Askvol4, Askvol5, Bid1, Bid2, Bid3, Bid4, Bid5, Bidvol1, Bidvol2, Bidvol3, Bidvol4, Bidvol5, Update, Time ");
            strSql.Append(" FROM tbl_Stock_MinInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public static DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" Symbol, Name, Type, Open, High, Low, Status, Price, Yestclose, Percent, Updown, Arrow, Volume, Turnover, Ask1, Ask2, Ask3, Ask4, Ask5, Askvol1, Askvol2, Askvol3, Askvol4, Askvol5, Bid1, Bid2, Bid3, Bid4, Bid5, Bidvol1, Bidvol2, Bidvol3, Bidvol4, Bidvol5, Update, Time ");
            strSql.Append(" FROM tbl_Stock_MinInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public static int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbl_Stock_MinInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.Symbol desc");
            }
            strSql.Append(")AS Row, T.*  from tbl_Stock_MinInfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbl_Stock_MinInfo";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/
    }
}